# -*- coding = utf-8 -*-
# @Time : 2021/3/29 13:35
# @Author : kk_J
# @File: spider.py
# @Software: PyCharm
import re
import time
import urllib.request
import urllib.error
import sqlite3
import os

from bs4 import BeautifulSoup


def init_db(dbpath):
    if os.path.exists(dbpath):
        print("目标数据库已存在，无需重新创建")
    else:
        sql = '''
            create table fayuandi
            (
            id integer primary key autoincrement,
            title varchar,
            size varchar,
            update_time varchar
            )
        '''
        conn = sqlite3.connect(dbpath)
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()


def get_html(link):
    head = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) "
                      "Chrome/88.0.4324.182 Safari/537.36 Edg/88.0.705.81 "
    }
    request = urllib.request.Request(link, headers=head)
    html = ""
    try:
        response = urllib.request.urlopen(request)
        html = response.read().decode('utf-8')
    except urllib.error.URLError as e:
        if hasattr(e, "code"):
            print(e.code)
        if hasattr(e, "reason"):
            print(e.reason)
    bs = BeautifulSoup(html, "html.parser")
    return bs


def savedata(recode, datapath, count):
    sql = '''
        insert into fayuandi(
        title, size, update_time
        ) VALUES (%s)
    ''' % recode
    conn = sqlite3.connect(datapath)
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()
    print('插入一条数据' + ' 共' + str(count) + '条')

baseurl = 'http://www.finndy.com/robot.php'
init_db('./data/data.db')
count = 0
for page in range(35, 38):
    bs0 = get_html(baseurl + '?orderby=dateline&page=' + str(page))
    trs = bs0.select('table.maintable tr')
    for tr in trs:
        # 数据标题
        title = tr.select('a.list-task-title')[0].string

        # 数据量
        size = tr.select('div.list-task-descs')[0].string
        size = re.findall('\d+.', size)[0]

        # 更新时间
        updata_time = tr.select('div.list-task-descs')[1].string
        updata_time = re.findall('\d{4}-\d{1,2}-\d{1,2}', updata_time)[0]

        recode = '"' + title + '",' + '"' + size + '",' + '"' + updata_time + '"'

        savedata(recode, './data/data.db', count)
        count += 1
        time.sleep(5)
